﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data;
using DTO;
using UTIL;

namespace DAO
{
    public class NguoiDungDao
    {
        public static bool ThemNguoiDung(NguoiDungDto one)
        {
            bool result = false;
            try
            {
                // tao ket noi CSDL 
                SqlConnection cnn = DataConnector.MakeConnection();

                // goi proc thuc thi them loai nguoi dung
                SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_themTaiKhoan");
                StoreUtilities.AddInputPara(cmd, "@username", SqlDbType.VarChar, one.TenDangNhap);
                StoreUtilities.AddInputPara(cmd, "@pass", SqlDbType.VarChar, one.MatKhau);
                StoreUtilities.AddInputPara(cmd, "@hoten", SqlDbType.NVarChar, one.HoTen);
                StoreUtilities.AddInputPara(cmd, "@ngaysinh", SqlDbType.DateTime, one.NgaySinh);
                StoreUtilities.AddInputPara(cmd, "@diachi", SqlDbType.NVarChar, one.DiaChi);
                StoreUtilities.AddInputPara(cmd, "@dienthoai", SqlDbType.VarChar, one.DienThoai);
                StoreUtilities.AddInputPara(cmd, "@email", SqlDbType.VarChar, one.Email);
                StoreUtilities.AddInputPara(cmd, "@sotaikhoan", SqlDbType.VarChar, one.SoTaiKhoan);
                StoreUtilities.AddInputPara(cmd, "@loainguoidung", SqlDbType.Int, one.LoaiNguoiDung);
                StoreUtilities.AddInputPara(cmd, "@diem", SqlDbType.Int, one.Diem);
                StoreUtilities.AddInputPara(cmd, "@trangthai", SqlDbType.NVarChar, one.TrangThai);
                int i = cmd.ExecuteNonQuery();
                if (i == 1)
                    result = true;

                // dong ket noi
                cnn.Close();
            }
            catch (Exception ex)
            {
                // bat loi 
                throw ex;

            }
            return result;
        }

        public static bool ThaydoiMatKhauNguoiDung(NguoiDungDto one, String matkhau)
        {
            bool result = false;
            try
            {
                // tao ket noi CSDL 
                SqlConnection cnn = DataConnector.MakeConnection();

                // goi proc thuc thi them loai nguoi dung
                SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_resetMatKhau");
                StoreUtilities.AddInputPara(cmd, "@matkhau", SqlDbType.VarChar, matkhau);
                StoreUtilities.AddInputPara(cmd, "@ID", SqlDbType.Int, one.Ma);

                int i = cmd.ExecuteNonQuery();
                if (i == 1)
                    result = true;

                // dong ket noi
                cnn.Close();
            }
            catch (Exception ex)
            {
                // bat loi 
            }
            return result;
        }

        public static bool PhanquyenNguoiDung(NguoiDungDto one, int loainguoidung)
        {
            bool result = false;
            try
            {
                // tao ket noi CSDL 
                SqlConnection cnn = DataConnector.MakeConnection();

                // goi proc thuc thi them loai nguoi dung
                SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_phanquyenTaiKhoan");
                StoreUtilities.AddInputPara(cmd, "@loainguoidung", SqlDbType.Int, loainguoidung);
                StoreUtilities.AddInputPara(cmd, "@ID", SqlDbType.Int, one.Ma);

                int i = cmd.ExecuteNonQuery();
                if (i == 1)
                    result = true;

                // dong ket noi
                cnn.Close();
            }
            catch (Exception ex)
            {
                // bat loi 
            }
            return result;
        }
        public static bool CapnhatTrangThaiNguoiDung(NguoiDungDto one, String trangthai)
        {
            bool result = false;
            try
            {
                // tao ket noi CSDL 
                SqlConnection cnn = DataConnector.MakeConnection();

                // goi proc thuc thi them loai nguoi dung
                SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_capnhattrangthaiTaiKhoan");
                StoreUtilities.AddInputPara(cmd, "@trangthai", SqlDbType.NVarChar, trangthai);
                StoreUtilities.AddInputPara(cmd, "@ID", SqlDbType.Int, one.Ma);

                int i = cmd.ExecuteNonQuery();
                if (i == 1)
                    result = true;

                // dong ket noi
                cnn.Close();
            }
            catch (Exception ex)
            {
                // bat loi 
            }
            return result;
        }
        public static bool XoaNguoiDung(NguoiDungDto one)
        {
            bool result = false;
            try
            {
                // tao ket noi CSDL 
                SqlConnection cnn = DataConnector.MakeConnection();
                List<NhanXetDto> nhanxet = NhanXetDao.LayNhanXetTheoNguoiDung(one.Ma);

                if (nhanxet.Count != 0)
                {

                    for (int j = 0; j < nhanxet.Count; j++)
                    {

                        NhanXetDao.XoaNhanXetTheoID(nhanxet[j].ID);
                    }

                }
                // goi proc thuc thi them loai nguoi dung
                SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_xoaTaiKhoan");
                StoreUtilities.AddInputPara(cmd, "@ID", SqlDbType.Int, one.Ma);

                int i = cmd.ExecuteNonQuery();
                if (i == 1)
                    result = true;

                // dong ket noi
                cnn.Close();
            }
            catch (Exception ex)
            {
                // bat loi 
            }
            return result;
        }

        public static bool CapnhatNguoiDung(NguoiDungDto one)
        {
            bool result = false;
            try
            {
                // tao ket noi CSDL 
                SqlConnection cnn = DataConnector.MakeConnection();

                // goi proc thuc thi them loai nguoi dung
                SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_capnhatTaiKhoan");
                // StoreUtilities.AddInputPara(cmd, "@pass", SqlDbType.VarChar, one.MatKhau);
                StoreUtilities.AddInputPara(cmd, "@hoten", SqlDbType.NVarChar, one.HoTen);
                StoreUtilities.AddInputPara(cmd, "@ngaysinh", SqlDbType.DateTime, one.NgaySinh);
                StoreUtilities.AddInputPara(cmd, "@diachi", SqlDbType.NVarChar, one.DiaChi);
                StoreUtilities.AddInputPara(cmd, "@dienthoai", SqlDbType.VarChar, one.DienThoai);
                StoreUtilities.AddInputPara(cmd, "@email", SqlDbType.VarChar, one.Email);
                StoreUtilities.AddInputPara(cmd, "@sotaikhoan", SqlDbType.VarChar, one.SoTaiKhoan);
                StoreUtilities.AddInputPara(cmd, "@diem", SqlDbType.Int, one.Diem);
                StoreUtilities.AddInputPara(cmd, "@ID", SqlDbType.Int, one.Ma);

                int i = cmd.ExecuteNonQuery();
                if (i == 1)
                    result = true;

                // dong ket noi
                cnn.Close();
            }
            catch (Exception ex)
            {
                // bat loi 
                throw ex;

            }
            return result;

        }

        public static NguoiDungDto LayNguoiDungTheoID(int ID)
        {
            // ket noi CSDL
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layTaiKhoanTheoID");
            StoreUtilities.AddInputPara(cmd, "@ID", SqlDbType.Int, ID);

            SqlDataReader dr = cmd.ExecuteReader();
            NguoiDungDto nguoidung = null;
            while (dr.Read())
            {
                nguoidung = new NguoiDungDto();
                nguoidung.Ma = (int)dr["ID"];
                nguoidung.TenDangNhap = (String)dr["tendangnhap"];
                nguoidung.MatKhau = (String)dr["matkhau"];
                nguoidung.HoTen = (String)dr["hoten"];
                nguoidung.NgaySinh = (DateTime)dr["ngaysinh"];
                nguoidung.DiaChi = (String)dr["diachi"];
                nguoidung.DienThoai = (String)dr["dienthoai"];
                nguoidung.Email = (String)dr["email"];
                nguoidung.SoTaiKhoan = (String)dr["sotaikhoan"];
                nguoidung.Diem = (int)dr["diem"];
                nguoidung.LoaiNguoiDung = (int)dr["loainguoidung"];
                nguoidung.TrangThai = (String)dr["trangthai"];
            }
            // dong ket noi
            cnn.Close();

            return nguoidung;
        }

        public static NguoiDungDto LayNguoiDungTheoTen(String ten)
        {
            // ket noi CSDL
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layNguoiDungTheoTenDangNhap");
            StoreUtilities.AddInputPara(cmd, "@tendangnhap", SqlDbType.VarChar, ten);

            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows == false)
                return null;
            NguoiDungDto nguoidung = null;
            while (dr.Read())
            {
                nguoidung = new NguoiDungDto();
                nguoidung.Ma = (int)dr["ID"];
                nguoidung.TenDangNhap = (String)dr["tendangnhap"];
                nguoidung.MatKhau = (String)dr["matkhau"];
                nguoidung.HoTen = (String)dr["hoten"];
                nguoidung.NgaySinh = (DateTime)dr["ngaysinh"];
                nguoidung.DiaChi = (String)dr["diachi"];
                nguoidung.DienThoai = (String)dr["dienthoai"];
                nguoidung.Email = (String)dr["email"];
                nguoidung.SoTaiKhoan = (String)dr["sotaikhoan"];
                nguoidung.Diem = (int)dr["diem"];
                nguoidung.LoaiNguoiDung = (int)dr["loainguoidung"];
                nguoidung.TrangThai = (String)dr["trangthai"];
            }
            // dong ket noi
            cnn.Close();

            return nguoidung;
        }

        public static List<String> LayTrangThaiNguoiDung()
        {
            SqlConnection cnn = DataConnector.MakeConnection();
            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachTrangThaiNguoiDung");


            SqlDataReader dr = cmd.ExecuteReader();

            List<String> danhsach = new List<String>();
            String str;
            while (dr.Read())
            {

                str = (String)dr["trangthai"];
                danhsach.Add(str);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }

        public static List<NguoiDungDto> LayNguoiDungTheoLoai(int loainguoidung)
        {
            // ket noi CSDL
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachTheoLoaiNguoiDung");
            StoreUtilities.AddInputPara(cmd, "@IDloainguoidung", SqlDbType.Int, loainguoidung);

            SqlDataReader dr = cmd.ExecuteReader();

            List<NguoiDungDto> danhsach = new List<NguoiDungDto>();
            NguoiDungDto nguoidung = null;
            while (dr.Read())
            {
                nguoidung = new NguoiDungDto();
                nguoidung.Ma = (int)dr["ID"];
                nguoidung.TenDangNhap = (String)dr["tendangnhap"];
                nguoidung.MatKhau = (String)dr["matkhau"];
                nguoidung.HoTen = (String)dr["hoten"];
                nguoidung.NgaySinh = (DateTime)dr["ngaysinh"];
                nguoidung.DiaChi = (String)dr["diachi"];
                nguoidung.DienThoai = (String)dr["dienthoai"];
                nguoidung.Email = (String)dr["email"];
                nguoidung.SoTaiKhoan = (String)dr["sotaikhoan"];
                nguoidung.Diem = (int)dr["diem"];
                nguoidung.LoaiNguoiDung = (int)dr["loainguoidung"];
                nguoidung.TrangThai = (String)dr["trangthai"];
                danhsach.Add(nguoidung);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }

        public static List<NguoiDungDto> LayNguoiDungTimKiem(String keyword)
        {
            // ket noi CSDL
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachTimKiemNguoiDung");
            StoreUtilities.AddInputPara(cmd, "@keyword", SqlDbType.NVarChar, keyword);

            SqlDataReader dr = cmd.ExecuteReader();

            List<NguoiDungDto> danhsach = new List<NguoiDungDto>();
            NguoiDungDto nguoidung = null;
            while (dr.Read())
            {
                nguoidung = new NguoiDungDto();
                nguoidung.Ma = (int)dr["ID"];
                nguoidung.TenDangNhap = (String)dr["tendangnhap"];
                nguoidung.MatKhau = (String)dr["matkhau"];
                nguoidung.HoTen = (String)dr["hoten"];
                nguoidung.NgaySinh = (DateTime)dr["ngaysinh"];
                nguoidung.DiaChi = (String)dr["diachi"];
                nguoidung.DienThoai = (String)dr["dienthoai"];
                nguoidung.Email = (String)dr["email"];
                nguoidung.SoTaiKhoan = (String)dr["sotaikhoan"];
                nguoidung.Diem = (int)dr["diem"];
                nguoidung.LoaiNguoiDung = (int)dr["loainguoidung"];
                nguoidung.TrangThai = (String)dr["trangthai"];
                danhsach.Add(nguoidung);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }

        public static List<NguoiDungDto> LayKhachHangTimKiem(String keyword)
        {
            // ket noi CSDL
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachTimKiemKhachHang");
            StoreUtilities.AddInputPara(cmd, "@keyword", SqlDbType.NVarChar, keyword);

            SqlDataReader dr = cmd.ExecuteReader();

            List<NguoiDungDto> danhsach = new List<NguoiDungDto>();
            NguoiDungDto nguoidung = null;
            while (dr.Read())
            {
                nguoidung = new NguoiDungDto();
                nguoidung.Ma = (int)dr["ID"];
                nguoidung.TenDangNhap = (String)dr["tendangnhap"];
                nguoidung.MatKhau = (String)dr["matkhau"];
                nguoidung.HoTen = (String)dr["hoten"];
                nguoidung.NgaySinh = (DateTime)dr["ngaysinh"];
                nguoidung.DiaChi = (String)dr["diachi"];
                nguoidung.DienThoai = (String)dr["dienthoai"];
                nguoidung.Email = (String)dr["email"];
                nguoidung.SoTaiKhoan = (String)dr["sotaikhoan"];
                nguoidung.Diem = (int)dr["diem"];
                nguoidung.LoaiNguoiDung = (int)dr["loainguoidung"];
                nguoidung.TrangThai = (String)dr["trangthai"];
                danhsach.Add(nguoidung);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }
        public static List<NguoiDungDto> LayTatCaNguoiDung()
        {
            // ket noi CSDL
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachTatCaNguoiDung");

            SqlDataReader dr = cmd.ExecuteReader();

            List<NguoiDungDto> danhsach = new List<NguoiDungDto>();
            NguoiDungDto nguoidung = null;
            while (dr.Read())
            {
                nguoidung = new NguoiDungDto();
                nguoidung.Ma = (int)dr["ID"];
                nguoidung.TenDangNhap = (String)dr["tendangnhap"];
                nguoidung.MatKhau = (String)dr["matkhau"];
                nguoidung.HoTen = (String)dr["hoten"];
                nguoidung.NgaySinh = (DateTime)dr["ngaysinh"];
                nguoidung.DiaChi = (String)dr["diachi"];
                nguoidung.DienThoai = (String)dr["dienthoai"];
                nguoidung.Email = (String)dr["email"];
                nguoidung.SoTaiKhoan = (String)dr["sotaikhoan"];
                nguoidung.Diem = (int)dr["diem"];
                nguoidung.LoaiNguoiDung = (int)dr["loainguoidung"];
                nguoidung.TrangThai = (String)dr["trangthai"];
                danhsach.Add(nguoidung);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }

        public static List<NguoiDungDto> LayTatCaKhachHang()
        {
            // ket noi CSDL
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachTatCaKhachHang");

            SqlDataReader dr = cmd.ExecuteReader();

            List<NguoiDungDto> danhsach = new List<NguoiDungDto>();
            NguoiDungDto nguoidung = null;
            while (dr.Read())
            {
                nguoidung = new NguoiDungDto();
                nguoidung.Ma = (int)dr["ID"];
                nguoidung.TenDangNhap = (String)dr["tendangnhap"];
                nguoidung.MatKhau = (String)dr["matkhau"];
                nguoidung.HoTen = (String)dr["hoten"];
                nguoidung.NgaySinh = (DateTime)dr["ngaysinh"];
                nguoidung.DiaChi = (String)dr["diachi"];
                nguoidung.DienThoai = (String)dr["dienthoai"];
                nguoidung.Email = (String)dr["email"];
                nguoidung.SoTaiKhoan = (String)dr["sotaikhoan"];
                nguoidung.Diem = (int)dr["diem"];
                nguoidung.LoaiNguoiDung = (int)dr["loainguoidung"];
                nguoidung.TrangThai = (String)dr["trangthai"];
                danhsach.Add(nguoidung);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }
        public static bool KiemtraTontai(NguoiDungDto one)
        {
            bool result = false;
            NguoiDungDto temp = NguoiDungDao.LayNguoiDungTheoTen(one.TenDangNhap);
            if (temp != null)
                result = true;

            return result;
        }

        //<NEW CODE>
        public static bool CongDiemNguoiDung(int id, int Diem)
        {
            NguoiDungDto user = NguoiDungDao.LayNguoiDungTheoID(id);
            user.Diem += Diem;
            return NguoiDungDao.CapnhatNguoiDung(user);
        }
        //</NEW CODE>
    }
}
